const fs = require('fs')
const ipcMain = require("electron").ipcMain
const { println } = require("../Console")
const Path = require('path')
const windows = global.windows;
const sqlite3 = global.Sqlite3.verbose();

const dbPath = Path.join(process.cwd(), "knciik_dnf_app.db") //根目录下
let database;


/** 数据库初始化 */
const init = () => {
    windows.setInitModule("JDBC");

    console.log("DATABASE path=", dbPath);

    if (!fs.existsSync(dbPath)) {
        console.log("creating database", dbPath);
        fs.openSync(dbPath, "w");
    }

    database = new sqlite3.Database(dbPath, (e) => {
        if (e)
            throw e
        console.log("connecting database,", dbPath);
        initDatabase();
    })
}

const initDatabase = () => {
    //查询配置表是否存在
    sqlite.tableExist("config").then(exist => {
        if (!exist) {
            const sqlPath = Path.join(__dirname, "default.sql");
            sqlite.doFile(sqlPath).then(() => {
                console.log("database init success");
                windows.setInitModuleSuccess("JDBC");
            })
            return;
        }

        console.log("database init success");
        windows.setInitModuleSuccess("JDBC");
    })
}



/** 封装常用查询 */
const sqlite = {
    createTest: () => new Promise((res, rej) => {
        try {
            const sql = `CREATE TABLE cats
            (
                id      INTEGER PRIMARY KEY AUTOINCREMENT,
                name    CHAR(50)           NOT NULL,
                age   INT
            );`

            database.exec(sql)
            console.log("JDBC createTest success")
            res(true)
        } catch (e) {
            console.log("JDBC createTest failed", e)
            rej(e)
        }
    }),

    insertTest: () => new Promise((res, rej) => {
        try {
            const sql = 'INSERT INTO cats (name, age) VALUES (?, ?)'
            const stmt = database.prepare(sql);
            stmt.run(['Jack', 2])
            stmt.run(['Tpm', 3])
            stmt.run(['Lucy', 4])
            console.log("JDBC insertTest success")
            res(true)
        } catch (e) {
            console.log("JDBC createTest failed", e)
            rej(e)
        }
    }),

    queryTest: () => new Promise((res, rej) => {
        database.all('SELECT * FROM cats where age = $age', {
            $age: 2,
        }, (e, data) => {
            if (e) {
                console.log("JDBC queryTest failed", e)
                println("查询失败：" + toString(e))
                rej(e)
            } else {
                console.log("JDBC queryTest success", data)
                println("查询成功!：" + toString(data))
                res(data)
            }
        })
    }),

    queryTest2: () => new Promise((res, rej) => {
        //查询一个不存在的表
        database.all('SELECT * FROM cats22 where age = $age', {
            $age: 2,
        }, (e, data) => {
            if (e) {
                console.log("JDBC queryTest2 failed", e)
                rej(e)
            } else {
                console.log("JDBC queryTest2 success", data)
                res(data)
            }
        })
    }),

    //通用查询  一条结果 返回对象
    get: (sql, params) => new Promise((res, rej) => {
        database.get(sql, params, (e, data) => {
            if (e)
                rej(e)
            else
                res(data)
        })
    }),

    //通用查询  多条结果 返回数组
    //没有params参数时不要传null,直接不传即可
    all: (sql, params) => new Promise((res, rej) => {
        database.all(sql, params, (e, data) => {
            if (e)
                rej(e)
            else
                res(data)
        })
    }),

    tableExist: (tableName) => new Promise((res, rej) => {
        const sql = `SELECT name FROM sqlite_master WHERE name='${tableName}'`;
        database.get(sql, (e, data) => {
            if (e)
                rej(e)
            else if (!data)
                res(false)
            else if (data.name == tableName)
                res(true)
            else
                res(false)
        })
    }),

    //导入sql脚本
    doFile: (path) => new Promise((res, rej) => {
        fs.readFile(path, "utf-8", (e, buffer) => {
            if (e) {
                console.log(e);
                rej(e);
                return;
            }

            if (buffer) {
                // console.log("read a file：",buffer)
                database.exec(buffer, e => {
                    if (e) {
                        console.log(e);
                        rej(e);
                        return;
                    }
                    res();
                });
            }
        })
    }),

    //菜单配置
    menus: {
        queryAll: () => new Promise((res, rej) => {
            database.all('SELECT * FROM menu ORDER BY solt ASC;', (e, data) => {
                if (e) {
                    console.error(e)
                    rej(e)
                } else {
                    res(data)
                }
            })
        }),

        queryAllWithEnable:() => new Promise((res, rej) => {
            database.all('SELECT * FROM menu where enable = 1 ORDER BY solt ASC;', (e, data) => {
                if (e) {
                    console.error(e)
                    rej(e)
                } else {
                    res(data)
                }
            })
        }),

        //批量更新id
        //@param data:Array  [{solt:int,name:string}]
        updateSolts: (datas) => new Promise((res, rej) => {
            database.serialize(() => {
                database.run("begin transaction");

                const sql = "update menu set solt=? where name=?";
                const stmt = database.prepare(sql);
                datas.forEach(data=>{
                    stmt.run(data.solt,data.name);
                })

                //这一步比较耗时
                database.run("commit", (e, d) => {
                    if (e) {
                        rej(e);
                        return;
                    } else {
                        res(true);
                    }
                });
            });
        }),

        updataEnableByName: (name, enable) => new Promise((res, rej) => {
            const sql = `update menu set enable=${enable} where name='${name}'`
            database.run(sql, (e, r) => {
                if (e) {
                    console.error(e)
                    rej(e)
                } else {
                    res(r)
                }
            })
        }),

        insert: (name, path, icon) => new Promise((res, rej) => {

        }),
    },

    //枚举配置
    enums: {
        queryAll: () => new Promise((res, rej) => {
            database.all('SELECT * FROM enum ORDER BY type ASC ,solt DESC;', (e, data) => {
                if (e) {
                    console.log(e)
                    rej(e)
                } else {
                    res(data)
                }
            })
        }),

        //查询所有类别,用于查询页面的类别筛选
        queryAllTypes: () => new Promise((res, rej) => {
            database.all('SELECT DISTINCT( type_zh ) AS type_zh FROM enum', (e, data) => {
                if (e) {
                    console.log(e)
                    rej(e)
                } else {
                    res(data)
                }
            })
        }),

        //全部字段模糊查询
        queryByText: (keyword) => new Promise((res, rej) => {
            const sql = `SELECT *  FROM enum WHERE 
                            type LIKE    '%${keyword}%' or 
                            type_zh LIKE '%${keyword}%' or 
                            name LIKE    '%${keyword}%' or 
                            name_zh LIKE '%${keyword}%' or 
                            remark LIKE  '%${keyword}%'`
            database.all(sql, (e, data) => {
                if (e) {
                    console.log(e)
                    rej(e)
                } else {
                    res(data)
                }
            })
        }),

        //根据type_zh查询
        queryByTypeZh: (type) => new Promise((res, rej) => {
            const sql = `select * from enum where type_zh = ? ORDER BY type ASC,solt ASC`
            database.all(sql, type, (e, data) => {
                if (e) {
                    console.log(e)
                    rej(e)
                } else {
                    res(data)
                }
            })
        }),

        /* data{
            id:
            name:
            name_zh:
            type:
            type_zh:
            solt:
            remark:
        } */
        update: (data) => new Promise((res, rej) => {
            with (data) {
                const sql = `update enum set
                    name = '${name}',
                    name_zh = '${name_zh}',
                    type = '${type}',
                    type_zh = '${type_zh}',
                    solt = ${solt},
                    remark = '${remark}'
                    where id= ${id}`

                database.run(sql, (err, rs) => {
                    if (err) {
                        rej(err)
                    } else {
                        res(rs);
                    }
                })
            }
        }),

        replaceInto: (id, name, nameZH, type, typeZH, solt, remark) => new Promise((res, rej) => {

        }),

        delete: (id) => new Promise((res, rej) => {

        }),
    },

    //服务器mysql配置
    mysql: {
        //查询上次使用的用户名,密码,ip，port
        getLastConfig() {
            let user = "root";
            let pass = "12345";
            let adress = "192.168.200.131";
            let port = "3306";

            return {
                user, pass, adress, port
            }
        }
    },

    //npk缓存
    //用来查询img在哪个npk
    npkIndex: {
        InsertBatch: (params) => {
            return new Promise((res, rej) => {

                database.serialize(() => {
                    // console.log("Loader begin transaction",result)
                    database.run("begin transaction");

                    const sql = "insert into npk_index(path,img,length) values(?,?,?)"
                    const stmt = database.prepare(sql);


                    params.forEach((param, index) => {
                        stmt.run(param)
                        // console.log(index);
                    });

                    //这一步比较耗时
                    database.run("commit", (e, d) => {
                        if (e) {
                            rej(e);
                            return;
                        } else {
                            res(true);
                        }
                    });
                });
            })
        },

        //测试查询
        queryTest: () => new Promise((res, rej) => {
            try {
                const sql = `SELECT * FROM
                ( SELECT value AS path FROM config WHERE name = 'imagePacks2' ),
                ( SELECT count( path ) AS count FROM "npk_index" )`


                database.get(sql, (err, data) => {
                    if (err) {
                        console.log("JDBC npkIndex.queryTest failed", err)
                        rej(err)
                    } else {
                        res(data)
                    }
                })
            } catch (err) {
                console.log("JDBC npkIndex.queryTest failed", err)
                rej(err)
            }
        }),

        //精确查询img
        queryImg: (img) => new Promise((res, rej) => {
            try {
                const sql = "select * from npk_index where img=?"
                database.all(sql, img, (err, data) => {
                    if (err) {
                        console.log("JDBC queryImg failed", err)
                        rej(err)
                    } else {
                        res(data)
                    }
                })
            } catch (err) {
                console.log("JDBC queryImg failed", err)
                rej(err)
            }
        }),

        //精确查询npk
        queryNpk: (npk) => new Promise((res, rej) => {
            try {
                const sql = "select * from npk_index where path=?"
                database.all(sql, npk, (err, data) => {
                    if (err) {
                        console.log("JDBC queryNpk failed", err)
                        rej(err)
                    } else {
                        res(data)
                    }
                })
            } catch (err) {
                console.log("JDBC queryNpk failed", err)
                rej(err)
            }
        }),

        queryById: (id) => new Promise((res, rej) => {
            try {
                const sql = `select * from npk_index where id=?`
                database.get(sql, id, (e, data) => {
                    if (e) {
                        console.log("JDBC queryById failed", e)
                        rej(e)
                    } else {
                        res(data)
                    }
                })
            } catch (e) {
                console.log("JDBC queryById failed", e)
                rej(e)
            }
        }),

        //用户搜索Img  分页查询
        queryImgLike: (img, limit, offset) => new Promise((res, rej) => {
            try {
                const sql = `select * from npk_index where img like '%${img.toLowerCase().replace(".img", "")}%' limit ${limit} offset ${offset}`;
                const rs = database.all(sql, (e, data) => {
                    if (e) {
                        console.error("JDBC queryImgLike failed", e)
                        rej(e)
                    } else {
                        res(data)
                    }
                })

            } catch (e) {
                console.error("JDBC queryImgLike failed", e)
                rej(e)
            }
        }),

        queryCountImgLike: (img) => new Promise((res, rej) => {
            try {
                const sql = `select count(*) as num from npk_index where img like '%${img.toLowerCase().replace(".img", "")}%' `;
                const rs = database.get(sql, (e, data) => {
                    if (e) {
                        console.error("JDBC queryCountImgLike failed", e)
                        rej(e)
                    } else {
                        res(data)
                    }
                })

            } catch (e) {
                console.error("JDBC queryCountImgLike failed", e)
                rej(e)
            }
        }),

        //用户搜索npk
        queryNpkLike: (npk, limit, offset) => new Promise((res, rej) => {
            try {
                const sql = `select * from npk_index where path like '%${npk.toLowerCase()}%' order by img limit ${limit} offset ${offset}`;
                const rs = database.all(sql, (e, data) => {
                    if (e) {
                        console.error("JDBC queryNpkLike failed", e)
                        rej(e)
                    } else {
                        res(data)
                    }
                })

            } catch (e) {
                console.error("JDBC queryNpkLike failed", e)
                rej(e)
            }
        }),

        queryCountNpkLike: (npk) => new Promise((res, rej) => {
            try {
                const sql = `select count(*) as num from npk_index where path like '%${npk.toLowerCase()}%' `;
                const rs = database.get(sql, (e, data) => {
                    if (e) {
                        console.error("JDBC queryCountNpkLike failed", e)
                        rej(e)
                    } else {
                        res(data)
                    }
                })

            } catch (e) {
                console.error("JDBC queryCountNpkLike failed", e)
                rej(e)
            }
        }),
    },

    //npk img文件索引缓存
    //用来快速读出png图片
    npkImg: {
        insert: (params) => new Promise((res, rej) => {
            try {
                const sql = "insert into npk_index values($path,$img,$img_index,$img_type,$img_comp,$img_size,$img_w,$img_h,$img_x,$img_y,$offset)"
                const stmt = database.prepare(sql);
                stmt.run(params)
                res(true)
            } catch (e) {
                console.log("JDBC createTest failed", e)
                rej(e)
            }
        }),

        queryImg: (img, index) => new Promise((res, rej) => {
            try {
                const sql = "select * from npk_img where img=? and img_index =?"
                res(database.all(sql, img, index))
            } catch (e) {
                console.log("JDBC createTest failed", e)
                rej(e)
            }
        })
    },

    //pvf缓存
    pvfIndex: {

    },

    //功能页面
    tools: {
        queryAll() {

        },

        //插入新功能页面
        insert(module_name, index_path) {

        },

        //修改功能页面是否可用
        updateEnable() {

        },

        //删除功能页面
        //注意调用前先删掉文件夹，不然下次启动又加进来了
        delete(module_name) {

        }
    },

    //配置查询
    config: {
        queryByName: (name) => new Promise((res, rej) => {
            const sql = "SELECT * FROM config WHERE name = ?";
            database.get(sql, name, (e, data) => {
                if (e) {
                    println("sqlite.config.queryByName error:" + e);
                    rej(e)
                } else {
                    res(data)
                }

            })
        }),

        insert: (name, value) => new Promise((res, rej) => {
            const sql = "insert into config values(?,?)";
            database.run(sql, name, value, (e, data) => {
                if (e) {
                    println("sqlite.config.insert error:" + e);
                    rej(e)
                } else {
                    res(true)
                }
            })
        }),

        update: (name, value) => new Promise((res, rej) => {
            const sql = "update config set value=? where name=?";
            database.run(sql, value, name, (e, data) => {
                if (e) {
                    println("sqlite.config.update error:" + e);
                    rej(e)
                } else {
                    res(true)
                }
            })
        }),

        replaceInto: (name, value) => new Promise((res, rej) => {
            const sql = `REPLACE INTO config(name,value) VALUES (?,?);`;
            database.run(sql, name, value, (e, data) => {
                if (e) {
                    println("sqlite.config.replaceInto error:" + e);
                    rej(e)
                } else {
                    res(true)
                }
            })
        }),
    }
}

init();

module.exports = {
    sqlite,
    database
}
